rm(list=ls())

library(lubridate)
library(readxl)
library(tidyverse)

home_directory = "[your home directory]"

scf_directory <- file.path(home_directory, "inputs")
master_scripts_directory<-file.path(home_directory, "DFAs_construction_code") 
reconciliation_directory <- file.path(home_directory, "outputs")

source(file.path(master_scripts_directory, "functions.R"))
b101h_db <- "[your b101.h data]"  # this is the public b101h database, grab from public website

filepath <- file.path(scf_directory, "dfa_scf_fa_nw_buscycle_detail.xlsx")

## Make SCF data frame using the function created
scf_data <- scf_balance_sheet(excel = filepath, hh = FALSE, income = FALSE) %>%
  dplyr::group_by(date) %>%
  dplyr::summarise_if(is.numeric, sum) %>%
  dplyr::mutate(non_fin = real_estate+con_durables, 
         debt_sec=us_gov_muni+corp_for_bonds, 
         ass_loans=oth_loans+morts_owed, 
         liab_loans = morts+cons_cred+dep_loans+oth_loans_adv) %>% 
  dplyr::mutate(fin = check_curr+time_dep+mmfs+debt_sec+ass_loans+corp_equ_mfs+life_ins+pension+non_corp_eq+misc_assets) %>% 
  dplyr::mutate(assets = non_fin+fin, 
         liab = liab_loans+unpaid_life_ins) %>% 
  dplyr::mutate(networth = assets - liab) %>% 
  select(date, assets, non_fin, real_estate, con_durables, fin, check_curr, time_dep, mmfs,  us_gov_muni, 
         corp_for_bonds,  oth_loans, morts_owed, corp_equ_mfs, life_ins, pension, non_corp_eq, misc_assets, 
         liab, morts, cons_cred, dep_loans, oth_loans_adv, unpaid_life_ins, networth) %>%
  dplyr::arrange(date)

series <- c(real_estate = "LM155035015.Q", 
            con_durables = "LM155111005.Q", 
            check_curr = "FL193020005.Q", 
            time_dep = "FL193030205.Q", 
            mmfs = "FL193034005.Q",  
            us_gov_muni = "FL193061005.Q",
            corp_for_bonds = "FL193063005.Q", 
            oth_loans = "FL153067005.Q", 
            morts_owed = "FL193065005.Q", 
            corp_equ_mfs = "LM193064005.Q", 
            life_ins = "FL153040005.Q", 
            pension = "FL153050005.Q", 
            non_corp_eq = "LM152090205.Q", 
            misc_assets = "FL153090005.Q", 
            morts = "FL153165105.Q", 
            cons_cred = "FL153166000.Q", 
            dep_loans = "FL193168005.Q", 
            oth_loans_adv = "FL193169005.Q", 
            unpaid_life_ins = "FL543077073.Q")

b101h <- fame2df(series, db = b101h_db, start = 19890930) %>%
  dplyr::mutate(date = as.yearqtr(date)) %>%
  dplyr::mutate(non_fin = real_estate+con_durables, 
         debt_sec=us_gov_muni+corp_for_bonds, 
         ass_loans=oth_loans+morts_owed, 
         liab_loans = morts+cons_cred+dep_loans+oth_loans_adv) %>% 
  dplyr::mutate(fin = check_curr+time_dep+mmfs+debt_sec+ass_loans+corp_equ_mfs+life_ins+pension+non_corp_eq+misc_assets) %>% 
  dplyr::mutate(assets = non_fin+fin, 
         liab = liab_loans+unpaid_life_ins) %>% 
  dplyr::mutate(networth = assets - liab) %>% 
  select(date, assets, non_fin, real_estate, con_durables, fin, check_curr, time_dep, mmfs,  us_gov_muni, 
         corp_for_bonds,  oth_loans, morts_owed, corp_equ_mfs, life_ins, pension, non_corp_eq, misc_assets, 
         liab, morts, cons_cred, dep_loans, oth_loans_adv, unpaid_life_ins, networth) %>%
  dplyr::filter(lubridate::quarter(date) == 3, lubridate::year(date) %in% seq(1989,2019,3)) %>%
  mutate_if(is.numeric, ~.*1e6)

scf_b101h_ratio <- full_join(rename_if(scf_data, is.numeric, function(x) paste0(x, ".scf")),
                             rename_if(b101h, is.numeric, function(x) paste0(x, ".b101h")),
                             by = "date") %>%
  dplyr::mutate(date = lubridate::year(date)) %>%
  pivot_longer(-c("date")) %>%
  separate(name, into = c("line", "measure"), sep = "[.]") %>%
  pivot_wider(id_cols = c(date, line), names_from = measure, values_from = value) %>%
  dplyr::mutate(ratio = round(100 * scf/b101h)) %>%
  select(date, line, ratio) 

scf_b101h_ratio_avg <- scf_b101h_ratio %>%
  dplyr::group_by(line) %>%
  dplyr::summarise(avg = round(mean(ratio)))

scf_b101h_ratio <- scf_b101h_ratio  %>%
  pivot_wider(id_cols = line, names_from = date, values_from = ratio) %>%
  rename_if(is.numeric, function(x) paste0("scf_", x)) %>%
  left_join(scf_b101h_ratio_avg, by = "line")

b101h_2019 <- dplyr::filter(b101h, date == max(date)) %>%
  pivot_longer(-date, names_to = "line", values_to = "fa_2019q3_value") %>%
  select(-date)

scf_2019 <- dplyr::filter(scf_data, date == max(date)) %>%
  pivot_longer(-date, names_to = "line", values_to = "scf_2019_value") %>%
  select(-date)

scf_b101h_ratio <- scf_b101h_ratio %>%
  left_join(b101h_2019, by = "line") %>%
  left_join(scf_2019, by = "line") %>%
  mutate_at(vars(fa_2019q3_value, scf_2019_value), ~round(./1e9)) 

write_csv(scf_b101h_ratio, file.path(reconciliation_directory, "reconciliation_table.csv"))


# Create LaTeX version ----------------------------------------------------

recon <- scf_b101h_ratio %>% 
  pivot_longer(-line, names_to = "year") %>% 
  mutate(year = str_remove_all(year, "scf_"))

recon <- split(recon, f = recon$line)

for (line in names(recon)) {
  recon[[line]] <- split(recon[[line]], f = recon[[line]]$year)
  
  for (year in names(recon[[line]])) {
    recon[[line]][[year]] <- recon[[line]][[year]] %>%
      select(value)
    
    recon[[line]][[year]] <- as.numeric(recon[[line]][[year]])
  }
}

library(brew)

brew(file = file.path(reconciliation_directory, "reconciliation_table.brew"), 
     output = file.path(reconciliation_directory, "reconciliation_table.tex"))
